
*************************************************************************************************************************************;
*   Code Name: 	Part4_Wealth_returns.sas
  	Author: 	Thomas Jansson, Sveriges Riksbank
	Updated: 	June 15, 2023
	Goal: 		Use individual asset information (from LINDA KURU datasets) to estimate the annual lagged return on risky financial 
				assets (at the household level). Not that wealth data is only available from 1999 to 2007.
*************************************************************************************************************************************;

*************************************************************************************************************************************;
** Import and save stock and mutual fund price information (at the ISIN level). Includes both adjusted and unadjusted prices;
proc import datafile = "\\micro.intra\projekt\P0459$\P0459_gem\SAS DATA\Thomas\allmatchedprices2.csv" out = save.allmatchedprices2 
	dbms = csv replace; 
	getnames = yes;
run;

*************************************************************************************************************************************;
** Import and save exchange rates;
proc import datafile = "\\micro.intra\projekt\P0459$\P0459_gem\SAS DATA\Thomas\exchange_rates9707.csv" out = save.exchange_rates9707 
	dbms = csv replace;
 	getnames = yes;
run;

*************************************************************************************************************************************;
** Define macros **;

%macro imputereturns(dataset);

data &dataset;
	set &dataset;

		value_unadjT = price_unadj_final*n_stocks*EXCH_RATE_PER_USD;						*value of stock portfolio using current unadjusted prices and current number of stocks;
		value_unadj_lagT = price_unadj_final*lag_n_stocks*EXCH_RATE_PER_USD;				*value of stock portfolio using current unadjusted prices and lagged number of stocks;

		value_adjT = price_adj_final2*n_stocks*EXCH_RATE_PER_USD;							*value of stock portfolio using current adjusted prices and current number of stocks;
		value_adj_lagT = price_adj_final2*lag_n_stocks*EXCH_RATE_PER_USD;					*value of stock portfolio using current adjusted prices and lagged number of stocks;

		value_adj_nlagT = lag_price_adj_final2*lag_n_stocks*lag_EXCH_RATE_PER_USD;			*value of stock portfolio using lagged adjusted prices and lagged number of stocks;
		value_unadj_nlagT = lag_price_unadj_final*lag_n_stocks*lag_EXCH_RATE_PER_USD;		*value of stock portfolio using lagged unadjusted prices and lagged number of stocks;

		stocks_w_priceT = n_stocks*priceinfo;												*number of shares with price information;
		stocks_w_adjpriceT = n_stocks*adj_dummy;											*number of shares with adjusted price information;
		stocks_w_unadjpriceT = n_stocks*unadj_dummy;										*number of shares with unadjusted price information;

		if lag_price_adj_final2>0 and price_adj_final2>0 and lag_n_stocks>0 then adjprice2perT = 1; else adjprice2perT = 0; 		*indicator variable if positive lagged holdings of a stock and there are lagged and current adjusted prices;
		if lag_price_unadj_final>0 and price_unadj_final>0 and lag_n_stocks>0 then unadjprice2perT = 1; else unadjprice2perT = 0;	*indicator variable if positive lagged holdings of a stock and there are lagged and current unadjusted prices;

		*if current and/or lagged unadjusted or adjusted prices are missing, use the information that is available to estimate imputed values (denoted by '2' in the name);
		if adjprice2perT = 1 then value_adj2_lagT = value_adj_lagT;
		if adjprice2perT = 1 then value_adj2_nlagT = value_adj_nlagT;
		
		if adjprice2perT = 1 and unadjprice2perT = 1 then value_unadj2_lagT = value_unadj_lagT;
		if adjprice2perT = 1 and unadjprice2perT = 1 then value_unadj2_nlagT = value_unadj_nlagT;

		if adjprice2perT = 1 and unadjprice2perT = 0 and value_unadj_lagT>0 then value_unadj2_lagT = value_unadj_lagT;
		if adjprice2perT = 1 and unadjprice2perT = 0 and value_unadj_lagT>0 then value_unadj2_nlagT = value_unadj_lagT*(value_adj_nlagT/value_adj_lagT);

		if adjprice2perT = 1 and unadjprice2perT = 0 and value_unadj_nlagT>0 then value_unadj2_nlagT = value_unadj_nlagT;
		if adjprice2perT = 1 and unadjprice2perT = 0 and value_unadj_nlagT>0 then value_unadj2_lagT = value_unadj_nlagT*(value_adj_lagT/value_adj_nlagT);

		if adjprice2perT = 1 and unadjprice2perT = 0 and value_unadj_lagT =. and value_unadj_nlagT =. then value_unadj2_lagT = value_adj_lagT; *use the adjusted prices;
		if adjprice2perT = 1 and unadjprice2perT = 0 and value_unadj_lagT =. and value_unadj_nlagT =. then value_unadj2_nlagT = value_adj_nlagT; *use the adjusted prices;

		if adjprice2perT = 0 and unadjprice2perT = 1 then value_adj2_lagT = value_unadj_lagT; *use the unadjusted prices;
 		if adjprice2perT = 0 and unadjprice2perT = 1 then value_adj2_nlagT = value_unadj_nlagT; *use the unadjusted prices;
		if adjprice2perT = 0 and unadjprice2perT = 1 then value_unadj2_lagT = value_unadj_lagT;
		if adjprice2perT = 0 and unadjprice2perT = 1 then value_unadj2_nlagT = value_unadj_nlagT;

		value_adj2T = value_adj2_lagT*(n_stocks/lag_n_stocks);
		value_unadj2T = value_unadj2_lagT*(n_stocks/lag_n_stocks);		                        

	drop merge;
run;

%mend;

%macro sort(dataset, variable);

proc sort data = &dataset;
	by &variable;
run;

%mend sort;

%macro sortnodupkey(dataset, variable);

proc sort data = &dataset nodupkey;
	by &variable;
run;

%mend sortnodupkey;
*************************************************************************************************************************************;

*********************;
***** KURU data *****;
*********************;

** Use stock and mutual fund holdings from the KURU files;
%macro loop1aaa;
%do I = 1999 %to 2007;

%let lagyear = %eval(&I-1);

data kuru31_&I;
  	set kuru.kuru&I._ku31 (keep = nybidnr BKUAUT BISIN KKUUTD);
		Kuru31 = 1;
run;

data kuru33_&I;
  	set kuru.kuru&I._ku33 (keep = nybidnr BKUAUT BISIN);
		Kuru33 = 1;
run;

%sort(kuru31_&I, nybidnr BISIN);
%sort(kuru33_&I, nybidnr BISIN);

data holdings31_&I (keep = nybidnr BISIN n_stocks31 kuru31);				
   set kuru31_&I;
   by nybidnr bisin;
   if first.bisin then do;
         n_stocks31 = 0;
      end;
   		n_stocks31 + BKUAUT;
 	if last.bisin then output;
run;

data holdings33_&I (keep = nybidnr BISIN n_stocks33 kuru33);				
   set kuru33_&I;
   by nybidnr bisin;
   if first.bisin then do;
         n_stocks33 = 0;
      end;
   		n_stocks33 + BKUAUT;
 	if last.bisin then output;
run;

%sortnodupkey(holdings31_&I, nybidnr bisin);
%sortnodupkey(holdings33_&I, nybidnr bisin);

*Merge KURU31 and KURU33 holdings;
data holdings_&I; 
	merge holdings31_&I holdings33_&I;
		by nybidnr bisin;
	if n_stocks31>0 or n_stocks33>0; *only positive holdings;
		n_stocks = sum(of n_stocks31 n_stocks33);
		label n_stocks = 'Num of shares (KURU31 & 33)';
run;

*stock and mutual fund price information;
data allmatchedprices&I;
	set save.allmatchedprices2;
		where year = &I;
	rename isin = bisin;
run;

%sortnodupkey(holdings_&I, bisin nybidnr);
%sortnodupkey(allmatchedprices&I, bisin);

*merge all individual holdings with prices;
data save.holdings_&I;
	merge allmatchedprices&I(keep = bisin price_unadj_final price_adj_final price_adj_final2 in = in_prices) holdings_&I(in = in_holdings);
		by bisin;
	if in_holdings = 1;
		merge = 1;
		if price_unadj_final =. then unadj_dummy = 0 ; else unadj_dummy = 1;
		if price_adj_final2 =. then adj_dummy = 0 ; else adj_dummy = 1;
		priceinfo = in_prices; *the ISIN is in the allmatchedprices dataset;
run;

%sort(save.holdings_&I, nybidnr);

*************************************************************************************************************************************;
*count the number of ISINs an individual holds;
data num_isins&I (keep = nybidnr num_isins);				
   set save.holdings_&I;
   	where n_stocks>0;
   		by nybidnr;
   if first.nybidnr then do;
         num_isins = 0;
	 end;
   		num_isins + 1;
 	if last.nybidnr then output;
run;

*with price info;
data num_isinsPRICE&I (keep = nybidnr num_isinsprice);				
   set save.holdings_&I;
   	where n_stocks>0 and priceinfo = 1;
   		by nybidnr;
   if first.nybidnr then do;
         num_isinsprice = 0;
	 end;
   		num_isinsprice + 1;
 	if last.nybidnr then output;
run;

*with adjusted prices;
data num_isinsadjPRICE&I (keep = nybidnr num_isinsadjprice);				
   set save.holdings_&I;
   	where n_stocks>0 and adj_dummy = 1;
   		by nybidnr;
   if first.nybidnr then do;
         num_isinsadjprice = 0;
	 end;
   		num_isinsadjprice + 1;
 	if last.nybidnr then output;
run;

*with unadjusted prices;
data num_isinsunadjPRICE&I (keep = nybidnr num_isinsunadjprice);				
   set save.holdings_&I;
   	where n_stocks>0 and unadj_dummy = 1;
   		by nybidnr;
   if first.nybidnr then do;
         num_isinsunadjprice = 0;
	 end;
   		num_isinsunadjprice + 1;
 	if last.nybidnr then output;
run;

*merge at individual level;
data save.num_isins&I;
 	merge num_isins&I num_isinsPRICE&I num_isinsadjPRICE&I num_isinsunadjPRICE&I;
		by nybidnr;
			if num_isins>0 and num_isinsprice =. then num_isinsprice = 0;
			if num_isins>0 and num_isinsunadjprice =. then num_isinsunadjprice = 0;
			if num_isins>0 and num_isinsadjprice =. then num_isinsadjprice = 0;
run; 

*************************************************************************************************************************************;
*Number of stocks (ISINs) with and without prices;
proc sort data = save.holdings_&I out = save.priceISIN&I (keep = bisin priceinfo rename = (priceinfo = priceinfo&I)) nodupkey;
	where n_stocks>0; 
		by bisin;
run;

%sort(save.holdings_&I, bisin nybidnr);

data save.holdings_agg&I (keep = bisin shares_agg prices holders year);				
 set save.holdings_&I;
 	where n_stocks>0;
   		by bisin;
   if first.bisin then do;
         shares_agg = 0;
		 prices = 0;
		 holders = 0;
      end;
   		shares_agg + n_stocks;
		prices + priceinfo;
		holders + 1; 
		year = &I;
 	if last.bisin then output;

run;

data holdings_noprice&I;
	set save.holdings_agg&I;
		where shares_agg>0 and prices = 0;
run;

data holdings_price&I;
	set save.holdings_agg&I;
		where shares_agg>0 and prices > 0;
run;

*Estimate the number of stocks (ISINs) with price info and without;
data holdings_agg2&I;				
	set save.holdings_agg&I;
		where shares_agg>0 and bisin>'1';
			if prices>0 then priceinfo = 1;
			else priceinfo = 0;
run;

%sort(holdings_agg2&I, priceinfo);

data save.holdings_agg2&I (keep = priceinfo shares_aggT n_isins year);				
 set holdings_agg2&I;
   by priceinfo;
   if first.priceinfo then do;
        shares_aggT = 0;
		n_isins = 0;
	end;
   		shares_aggT + shares_agg;
		n_isins + 1;
	if last.priceinfo then output;
run;

%end;
%mend;
%loop1aaa;
run;

*************************************************************************************************************************************;

%macro loop1aaaqq;
%do I = 1999 %to 2007;
%let lagyear = %eval(&I-1);

*add exchange rates to the holdings data (since some prices are in other currencies than SEK);
data exchange_rate&I;
	set save.exchange_rates9707;
		where date = &I.1231 and ISO_CURRENCY = 'SEK';
			merge = 1;
run;

data save.holdings_&I;
	merge exchange_rate&I save.holdings_&I;
		by merge;
run;

%sortnodupkey(save.holdings_&I, nybidnr bisin);

*************************************************************************************************************************************;
** Create a dataset with holdings and prices from the current year and from the previous year;

%if &I>=2000 %then %do;
%sortnodupkey(save.holdings_&lagyear, nybidnr bisin);

data temp.holdings2_&I;
	merge save.holdings_&I 
		save.holdings_&lagyear (rename = (n_stocks = lag_n_stocks EXCH_RATE_PER_USD = lag_EXCH_RATE_PER_USD price_unadj_final = lag_price_unadj_final price_adj_final2 = lag_price_adj_final2) 
			keep = nybidnr bisin n_stocks EXCH_RATE_PER_USD price_unadj_final price_adj_final2);
		by nybidnr bisin;
run;
	%end;

	%if &I=1999 %then %do; *if 1999, then lagged variables are missing;

data temp.holdings2_&I;
	set save.holdings_&I;
		lag_n_stocks = .;
		lag_EXCH_RATE_PER_USD = .; 
		lag_price_unadj_final =.;
		lag_price_adj_final2 =.;
run;
	%end;

*use the imputereturns macro;
%imputereturns(temp.holdings2_&I);

%sort(temp.holdings2_&I, nybidnr);

*************************************************************************************************************************************;
** Merge total values of the stock portfolio at the individual level;
data save.stockvalues&I (keep = nybidnr value_unadj value_unadj_lag value_adj value_adj_lag value_adj_nlag value_unadj_nlag kuru_31 kuru_33
						stocks_w_price stocks_w_adjprice stocks_w_unadjprice stocks_n_total adjprice2per unadjprice2per 
						value_adj2_lag value_adj2_nlag value_unadj2_lag value_unadj2_nlag value_adj2 value_unadj2);				
   set temp.holdings2_&I;
   	by nybidnr;
   		if first.nybidnr then do;

		 value_adj = 0;
		 value_unadj = 0;
		 value_adj_lag = 0;
		 value_adj_nlag = 0;
		 value_unadj_lag = 0;
		 value_unadj_nlag = 0;

		 value_adj2 = 0;
		 value_unadj2 = 0;
		 value_adj2_lag = 0;
		 value_adj2_nlag = 0;
		 value_unadj2_lag = 0;
		 value_unadj2_nlag = 0;

		 kuru_31 = 0;
		 kuru_33 = 0;

		 stocks_w_price = 0;
		 stocks_w_adjprice = 0;
		 stocks_w_unadjprice = 0;
		 stocks_n_total = 0;

		 adjprice2per = 0;
		 unadjprice2per = 0;

end;

		value_adj + value_adjT;
		value_unadj + value_unadjT;	
		value_adj_lag + value_adj_lagT;
		value_adj_nlag + value_adj_nlagT;
		value_unadj_lag + value_unadj_lagT;
		value_unadj_nlag + value_unadj_nlagT;

		value_adj2 + value_adj2T;
		value_unadj2 + value_unadj2T;
		value_adj2_lag + value_adj2_lagT;
		value_adj2_nlag + value_adj2_nlagT;
		value_unadj2_lag + value_unadj2_lagT;
		value_unadj2_nlag + value_unadj2_nlagT;

		kuru_31 + kuru31;
		kuru_33 + kuru33;

		stocks_w_price + stocks_w_priceT;
		stocks_w_adjprice + stocks_w_adjpriceT;
		stocks_w_unadjprice + stocks_w_unadjpriceT;
		stocks_n_total + n_stocks;

		adjprice2per + adjprice2perT;
		unadjprice2per + unadjprice2perT;

 	if last.nybidnr then output;
run;

proc datasets lib = temp;
	delete holdings2_&I;
run;

*label key variables;
data save.stockvalues&I;
	set save.stockvalues&I;

label value_unadj = 'Value stocks unadj';				*value of the stock portfolio using current unadjusted prices and current number of stocks;
label value_unadj_lag = 'Value stocks unadj n(t-1)'; 	*value of the stock portfolio using current unadjusted prices and lagged number of stocks;
label value_adj = 'Value stocks adj';					*value of the stock portfolio using current adjusted prices and current number of stocks;
label value_adj_lag = 'Value stocks adj n(t-1)';		*value of the stock portfolio using current adjusted prices and lagged number of stocks;
label value_adj_nlag = 'Value stocks t-1 adj';			*value of the stock portfolio using lagged adjusted prices and lagged number of stocks;
label value_unadj_nlag = 'Value stocks t-1 unadj';		*value of the stock portfolio using lagged unadjusted prices and lagged number of stocks;

label value_unadj2 = 'Value stocks unadj';				*(adjusted) value of the stock portfolio using current unadjusted prices  and current number of stocks;
label value_unadj2_lag = 'Value stocks unadj n(t-1)'; 	*(adjusted) value of the stock portfolio using current unadjusted prices and lagged number of stocks;
label value_adj2 = 'Value stocks adj';					*(adjusted) value of the stock portfolio using current adjusted prices  and current number of stocks;
label value_adj2_lag = 'Value stocks adj n(t-1)';		*(adjusted) value of the stock portfolio using current adjusted prices and lagged number of stocks;
label value_adj2_nlag = 'Value stocks t-1 adj';			*(adjusted) value of the stock portfolio using lagged adjusted prices and lagged number of stocks;
label value_unadj2_nlag = 'Value stocks t-1 unadj';		*(adjusted) value of the stock portfolio using lagged unadjusted prices and lagged number of stocks;

label stocks_w_price = 'Num of shares with price data';
label stocks_n_total = 'Num of shares in total';

run;

Proc datasets LIB = work KILL;
run;

%end;
%mend;
%loop1aaaqq;
run;

*************************************************************************************************************************************;
%let wvars = value_unadj value_adj2 value_unadj2 value_adj2_lag value_adj2_nlag value_unadj2_lag value_unadj2_nlag;
%let hhwvars = hhvalue_unadj hhvalue_adj2 hhvalue_unadj2 hhvalue_adj2_lag hhvalue_adj2_nlag hhvalue_unadj2_lag hhvalue_unadj2_nlag ;
%let Nw = 7;
*************************************************************************************************************************************;

** Aggregate values for variables listed above at the household level and calculate returns;
%macro m3b(f_or_i);
%do y = 1999 %to 2007;

proc sort data = save.stockvalues&y;
	by nybidnr;
run;

*get information about household id for each individual;
proc sort data = grund.lindafamily&f_or_i&y out = lindafamily&f_or_i&y;
	by nybidnr;
run;

data stockvalues&f_or_i&y;
	merge save.stockvalues&y lindafamily&f_or_i&y (in = in_family);
		by nybidnr;
	if in_family=1;
run; 

proc sort data = stockvalues&f_or_i&y;
	by nybidnrh;
run;

*aggregate stock portfolio values at household level;
data hhstockvalues&f_or_i&y;
	set stockvalues&f_or_i&y;
	by nybidnrh;
	retain &hhwvars 0;
	if first.nybidnrh then do;
		%do k = 1 %to &Nw;
			%scan(&hhwvars,&k) = 0;
		%end;
	end;
		%do k = 1 %to &Nw;
		%scan(&hhwvars,&k) = %scan(&hhwvars,&k) + %scan(&wvars,&k);
		%end;
	if last.nybidnrh then do;
		output;
	end;
	keep nybidnrh &hhwvars;
run;

*return calculation;
data hhstockvalues&f_or_i&y;
	set hhstockvalues&f_or_i&y;
		if hhvalue_adj2_lag>0 and hhvalue_adj2_nlag>0 then return_lag1 = hhvalue_adj2_lag/hhvalue_adj2_nlag; *annual returns, using adjusted values;
run;

proc sort data = grund.lindafamily&f_or_i&y out = lindafamily&f_or_i&y;
	by nybidnrh;
run;

data save.indstockvalues&f_or_i&y;
	merge hhstockvalues&f_or_i&y lindafamily&f_or_i&y;
		by nybidnrh;
	rename nybidnr = id;
	drop nybidnrh;
run;

*create a dataset containing id and lagged return;
data save.returns&f_or_i&y;
	set save.indstockvalues&f_or_i&y;
		keep id return_lag1;
run;

proc sort data = save.returns&f_or_i&y;
	by id;
run;

*compare with previous version;
proc compare base = save.returns&f_or_i&y
				compare = saveold.returns&f_or_i&y listall
CRITERION = 0.1 METHOD = exact;
run;

%end;
%mend;
%m3b(f);
%m3b(i);


*************************************************************************************************************************************;
** END OF CODE 
*************************************************************************************************************************************;

